<?php
require_once('MySqlConnection.php');
require_once('Practice.php');

class PracticeDAL{
	
	private $_connection;
	//Singleton pattern
    private static $_instance = null;
	public static function Instance(){
		if (self::$_instance == null) self::$_instance = new PracticeDAL();
		return self::$_instance;
	}
	private function __construct(){
		include_once('./include/db.inc');
		$this->_connection = MySqlConnection::Instance($server,$user,$password,$database);
	}
	
	public function getPractices(){
		$this->_connection->Open();
		$sqlString = "select * from practice";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'practice');
	}
	
	public function getPracticeById($practiceId)
	{
		$this->_connection->Open();
		$sqlString = "SELECT p.*, u.first_name as user_first_name, u.last_name as user_last_name, u.avatar as user_avatar, a.street as address_street, a.house_number as address_house_number, a.box as address_box, a.city_name as address_city_name, l.postal_number as location_postal_number, l.id as location_id, prof.name as profession_name, c.id as country_id, d.district_id as district_id, a.latitude as latitude, a.longitude as longitude FROM practice p JOIN address a ON p.address_id = a.id JOIN location l ON a.location_id = l.id JOIN user u ON p.user_id = u.id JOIN profession prof ON p.profession_id = prof.id JOIN district d ON l.district_id = d.district_id JOIN country c ON d.country_id = c.id WHERE p.id = '{$practiceId}'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'practice');
	}
	
	public function addPractice($practice){
		include('./include/db.inc');
		$mysqli = new mysqli($server, $user, $password, $database);
		if(mysqli_connect_errno()) {
		  echo "Connection Failed: " . mysqli_connect_errno();
		  exit();
		}
		if($stmt = $mysqli->prepare("INSERT INTO practice(user_id,profession_id,address_id,corp_number,web,gsm,phone,infotext) VALUES(?,?,?,?,?,?,?,?)"))
		{
			$stmt->bind_param("iiisssss", $practice['user_id'],$practice['profession_id'],$practice['address_id'],$practice['corp_number'],$practice['web'],$practice['gsm'],$practice['phone'],$practice['infotext']);
			$success = $stmt->execute();
			if(!$success){
				echo "Inserting practice failed!";
			}
			else{
				$resultArray[] = $mysqli->insert_id;
				return $resultArray; 
			}
		}
		else{
			
			echo "Failed preparing insert practice statement!";
		}
		$mysqli -> close();
	}
	
	public function removePractice($practiceId){
		include('./include/db.inc');
		$mysqli = new mysqli($server, $user, $password, $database);
		if(mysqli_connect_errno()) {
		  echo "Connection Failed: " . mysqli_connect_errno();
		  exit();
		}
		if($stmt = $mysqli->prepare("DELETE FROM practice WHERE id = '?'")) {
			$stmt->bind_param("i", $practiceId);
			$success = $stmt->execute();
			if(!$success){
				echo "Removing practice failed!";
			}
		}
		else{
			
			echo "Failed preparing delete practice statement!";
		}
		$mysqli -> close();
	}
	
	public function updatePractice($practice) {
		include('./include/db.inc');
		$mysqli = new mysqli($server, $user, $password, $database);
		if(mysqli_connect_errno()) {
		  echo "Connection Failed: " . mysqli_connect_errno();
		  exit();
		}
		if($stmt = $mysqli->prepare("UPDATE practice SET user_id = ?, profession_id = ?, address_id = ?, corp_number = ?, web = ?, gsm = ?, phone = ?, infotext = ? WHERE id = ?")) {
			$stmt->bind_param("iiisssssi", $practice['user_id'], $practice['profession_id'], $practice['address_id'], $practice['corp_number'], $practice['web'], $practice['gsm'], $practice['phone'], $practice['infotext'], $practice['id']);
			$success = $stmt->execute();
			if(!$success){
				echo "Updating practice failed!";
			}
		}
		else{
			
			echo "Failed preparing update practice statement!";
		}
		$mysqli -> close();
	}
	
	public function getPracticesForUser($userId){
		$this->_connection->Open();
		$sqlString = "select * from practice where user_id = '{$userId}'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'practice');
	}
	
	public function getPracticesByPostalNumberOrLocationName($searchString, $countryId){
		$this->_connection->Open();
		$sqlString = "SELECT p.*, u.first_name as user_first_name, u.last_name as user_last_name, u.avatar as user_avatar, a.street as address_street, a.house_number as address_house_number, a.box as address_box, a.city_name as address_city_name, l.postal_number as location_postal_number, l.id as location_id, prof.name as profession_name, c.id as country_id, d.district_id as district_id, a.latitude as latitude, a.longitude as longitude FROM practice p JOIN address a ON p.address_id = a.id JOIN location l ON a.location_id = l.id JOIN user u ON p.user_id = u.id JOIN profession prof ON p.profession_id = prof.id JOIN district d ON l.district_id = d.district_id JOIN country c ON d.country_id = c.id WHERE (l.postal_number LIKE '%{$searchString}%' OR l.city_name LIKE '%{$searchString}%' OR a.city_name LIKE '%{$searchString}%') AND u.activated = 1 AND c.id = {$countryId}";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'practice');
	}
	
	public function getPracticesByAdvancedSearch($name, $countryId, $districtId, $professionId, $specializationId, $targetId, $problemId, $sex){
		$this->_connection->Open();
		
		$filterString = "";
		
		//return "fout: " + $name + " " + $countryId;
		
		if($countryId != -1){
			$filterString .= " AND c.id = '{$countryId}' ";
		}
		if($districtId != -1){
			$filterString .= " AND l.district_id = '{$districtId}' ";
		}
		if($professionId != -1){
			$filterString .= " AND prof.id = '{$professionId}' ";
		}
		if($sex != -1){
			$filterString .= " AND (SELECT sex FROM salutation sal WHERE sal.id = u.salutation_id) = '{$sex}%' ";
		}
		if($specializationId != -1){
			$filterString .= " AND EXISTS(SELECT * FROM userspecialization us WHERE us.specialization_id = '{$specializationId}' AND us.user_id = u.id) ";
		}
		if($targetId != -1){
			$filterString .= " AND EXISTS(SELECT * FROM usertarget ut WHERE ut.target_id = '{$targetId}' AND ut.user_id = u.id) ";
		}
		if($problemId != -1){
			$filterString .= " AND EXISTS(SELECT * FROM userproblem up WHERE up.problem_id = '{$problemId}' AND up.user_id = u.id) ";
		}
		
		$sqlString = "SELECT p.*, u.first_name as user_first_name, u.last_name as user_last_name, u.avatar as user_avatar, a.street as address_street, a.house_number as address_house_number, a.box as address_box, a.city_name as address_city_name, l.postal_number as location_postal_number, l.id as location_id, prof.name as profession_name, c.id as country_id, d.district_id as district_id, a.latitude as latitude, a.longitude as longitude FROM practice p JOIN address a ON p.address_id = a.id JOIN location l ON a.location_id = l.id JOIN user u ON p.user_id = u.id JOIN profession prof ON p.profession_id = prof.id JOIN district d ON l.district_id = d.district_id JOIN country c ON d.country_id = c.id WHERE concat_ws(' ',u.first_name,u.last_name) LIKE '%{$name}%' " . $filterString . "  AND u.activated = 1";
		
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'practice');
	}
	
	
	//MAPPING
	private function Mapping($queryData,$resultType){
		foreach($queryData as $data){
			$resultArray[] = $this->Map($data,$resultType);
		}
		return $resultArray;
	}
	
	private function Map($data,$resultType){
		$object = new Practice($data->id,$data->user_id,$data->profession_id,$data->address_id,$data->corp_number,$data->web,$data->gsm,$data->phone,$data->infotext, $data->user_first_name, $data->user_last_name, $data->user_avatar, $data->address_street, $data->address_house_number, $data->address_box, $data->address_city_name, $data->location_postal_number, $data->profession_name, $data->country_id, $data->location_id, $data->district_id, $data->latitude, $data->longitude);
		return $object;
	}
	
	
}

